/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.ProyectosInversionVO;
import com.informet.ipd.modelo.vo.ReportePOIVO;
import com.informet.ipd.modelo.vo.RolUsuarioVO;
import com.informet.ipd.modelo.vo.UbigeoVO;
import com.informet.ipd.modelo.vo.UsuarioVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class ProyectoInversionDao extends DBase {

    Connection conn = null;

    public ProyectoInversionDao() {
        super();
    }

    public void setComboDistrito(UbigeoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
        bean.setTX_NOMDIST(rs.getString("TX_NOMDIST"));

    }

    public void setListTableTituProg(ProyectosInversionVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_PYINV_PK(rs.getInt("NUM_PYINV_PK"));
        bean.setTX_NOMBREPYI(rs.getString("TX_NOMBREPYI"));
        bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
        bean.setTX_NOMDIST(rs.getString("TX_NOMDIST"));
        bean.setTX_DESCOBJ(rs.getString("TX_DESCOBJ"));
        bean.setTX_DESCOBJESP(rs.getString("TX_DESCOBJESP"));
        bean.setTX_NOMDPTO(rs.getString("TX_NOMDPTO"));
        bean.setTX_NOMPRV(rs.getString("TX_NOMPRV"));
        // Ini agregado
        bean.setNUM_CODOBJ_PK(rs.getString("codObj"));
        bean.setNUM_CODOBJESP_PK(rs.getString("codObjEsp"));
        bean.setNUM_CODACTPER_PK(Integer.parseInt(rs.getString("codActPer")));
        bean.setNUM_CODDPTO_PK(rs.getString("codDpto"));
        bean.setNUM_CODPRV_PK(rs.getString("codProvincia"));
        bean.setNUM_CODDIST_PK(rs.getString("codDistrito"));
        bean.setTX_RUTAARBP(rs.getString("ruta"));//
        bean.setTX_ADJUARBP(rs.getString("rutaAdjunto"));

        // Fin agregado

    }

    public void setReportePOI(ReportePOIVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODOBJ_PK(rs.getString("NUM_CODOBJ_PK"));
        bean.setTX_DESCOBJ(rs.getString("TX_DESCOBJ"));
        bean.setNUM_CODOBJESP_PK(rs.getString("NUM_CODOBJESP_PK"));
        bean.setTX_DESCOBJESP(rs.getString("TX_DESCOBJESP"));
        bean.setExpr1(rs.getString("Expr1"));
        bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
        bean.setNUM_CODACTPER_PK(rs.getString("NUM_CODACTPER_PK"));
        bean.setNUM_CODDEPEN_PK(rs.getString("NUM_CODDEPEN_PK"));

        bean.setExpr2(rs.getString("Expr2"));

        bean.setDT_INIACTPERC(rs.getString("DT_INIACTPERC"));

        bean.setDT_INIACTPER(rs.getString("DT_INIACTPER"));

        bean.setDT_TERACTPERC(rs.getString("DT_TERACTPERC"));

        bean.setDT_TERACTPER(rs.getString("DT_TERACTPER"));

        bean.setNUM_CODFISIMETAM_PK(rs.getString("NUM_CODFISIMETAM_PK"));

        bean.setNUM_MES1FISIMETAM(rs.getString("NUM_MES1FISIMETAM"));
        bean.setNUM_MES2FISIMETAM(rs.getString("NUM_MES2FISIMETAM"));
        bean.setNUM_MES3FISIMETAM(rs.getString("NUM_MES3FISIMETAM"));
        bean.setNUM_MES4FISIMETAM(rs.getString("NUM_MES4FISIMETAM"));
        bean.setNUM_MES5FISIMETAM(rs.getString("NUM_MES5FISIMETAM"));
        bean.setNUM_MES6FISIMETAM(rs.getString("NUM_MES6FISIMETAM"));

        bean.setNUM_MES7FISIMETAM(rs.getString("NUM_MES7FISIMETAM"));

        bean.setNUM_MES8FISIMETAM(rs.getString("NUM_MES8FISIMETAM"));

        bean.setNUM_MES9FISIMETAM(rs.getString("NUM_MES9FISIMETAM"));

        bean.setNUM_MES10FISIMETAM(rs.getString("NUM_MES10FISIMETAM"));
        bean.setNUM_MES11FISIMETAM(rs.getString("NUM_MES11FISIMETAM"));
        bean.setNUM_MES12FISIMETAM(rs.getString("NUM_MES12FISIMETAM"));
        bean.setNUM_MES1FISIMETAMC(rs.getString("NUM_MES1FISIMETAMC"));

        bean.setNUM_MES2FISIMETAMC(rs.getString("NUM_MES2FISIMETAMC"));
        bean.setNUM_MES3FISIMETAMC(rs.getString("NUM_MES3FISIMETAMC"));

        bean.setNUM_MES4FISIMETAMC(rs.getString("NUM_MES4FISIMETAMC"));

        bean.setNUM_MES5FISIMETAMC(rs.getString("NUM_MES5FISIMETAMC"));
        bean.setNUM_MES6FISIMETAMC(rs.getString("NUM_MES6FISIMETAMC"));
        bean.setNUM_MES7FISIMETAMC(rs.getString("NUM_MES7FISIMETAMC"));
        bean.setNUM_MES8FISIMETAMC(rs.getString("NUM_MES8FISIMETAMC"));
        bean.setNUM_MES9FISIMETAMC(rs.getString("NUM_MES9FISIMETAMC"));
        bean.setNUM_MES10FISIMETAMC(rs.getString("NUM_MES10FISIMETAMC"));
        bean.setNUM_MES11FISIMETAMC(rs.getString("NUM_MES11FISIMETAMC"));
        bean.setNUM_MES12FISIMETAMC(rs.getString("NUM_MES12FISIMETAMC"));
        bean.setNUM_CODMETAAPER_PK(rs.getString("NUM_CODMETAAPER_PK"));
        bean.setExpr3(rs.getString("Expr3"));
        bean.setNUM_CODFINAMETAM_PK(rs.getString("NUM_CODFINAMETAM_PK"));

        bean.setNUM_MES1FINAMETAM(rs.getString("NUM_MES1FINAMETAM"));

        bean.setNUM_MES2FINAMETAM(rs.getString("NUM_MES2FINAMETAM"));

        bean.setNUM_MES3FINAMETAM(rs.getString("NUM_MES3FINAMETAM"));

        bean.setNUM_MES4FINAMETAM(rs.getString("NUM_MES4FINAMETAM"));

        bean.setNUM_MES5FINAMETAM(rs.getString("NUM_MES5FINAMETAM"));

        bean.setNUM_MES6FINAMETAM(rs.getString("NUM_MES6FINAMETAM"));

        bean.setNUM_MES7FINAMETAM(rs.getString("NUM_MES7FINAMETAM"));
        bean.setNUM_MES9FINAMETAM(rs.getString("NUM_MES9FINAMETAM"));

        bean.setNUM_MES10FINAMETAM(rs.getString("NUM_MES10FINAMETAM"));

        bean.setNUM_MES8FINAMETAM(rs.getString("NUM_MES8FINAMETAM"));

        bean.setNUM_MES11FINAMETAM(rs.getString("NUM_MES11FINAMETAM"));

        bean.setNUM_MES12FINAMETAM(rs.getString("NUM_MES12FINAMETAM"));

        bean.setNUM_MES1FINAMETAMC(rs.getString("NUM_MES1FINAMETAMC"));

        bean.setNUM_MES2FINAMETAMC(rs.getString("NUM_MES2FINAMETAMC"));

        bean.setNUM_MES3FINAMETAMC(rs.getString("NUM_MES3FINAMETAMC"));

        bean.setNUM_MES4FINAMETAMC(rs.getString("NUM_MES4FINAMETAMC"));

        bean.setNUM_MES5FINAMETAMC(rs.getString("NUM_MES5FINAMETAMC"));

        bean.setNUM_MES6FINAMETAMC(rs.getString("NUM_MES6FINAMETAMC"));

        bean.setNUM_MES7FINAMETAMC(rs.getString("NUM_MES7FINAMETAMC"));

        bean.setNUM_MES8FINAMETAMC(rs.getString("NUM_MES8FINAMETAMC"));

        bean.setNUM_MES9FINAMETAMC(rs.getString("NUM_MES9FINAMETAMC"));

        bean.setNUM_MES10FINAMETAMC(rs.getString("NUM_MES10FINAMETAMC"));

        bean.setNUM_MES11FINAMETAMC(rs.getString("NUM_MES11FINAMETAMC"));

        bean.setNUM_MES12FINAMETAMC(rs.getString("NUM_MES12FINAMETAMC"));

        bean.setExpr4(rs.getString("Expr4"));
        bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
    }

    public void setListProgId(ProyectosInversionVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_PYINV_PK(rs.getInt("NUM_PYINV_PK"));
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setNUM_NROREGPYI(rs.getInt("NUM_NROREGPYI"));
        bean.setNUM_SANEAPYI(rs.getInt("NUM_SANEAPYI"));
        bean.setNUM_SANEAPYI2(rs.getInt("NUM_SANEAPYI2"));
        bean.setNUM_SANEAPYI3(rs.getInt("NUM_SANEAPYI3"));
        bean.setNUM_CRITERIOPYI(rs.getInt("NUM_CRITERIOPYI"));
        bean.setNUM_SNIPPYI(rs.getInt("NUM_SNIPPYI"));
        bean.setNUM_PYINV_PK(rs.getInt("NUM_PYINV_PK"));
        bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
        bean.setNUM_ANIOPRESUPYI_PK(rs.getInt("NUM_ANIOPRESUPYI_PK"));
        bean.setNUM_PRESUPYI(rs.getBigDecimal("NUM_PRESUPYI"));

        bean.setDT_TERPYI(rs.getString("DT_TERPYI"));
        bean.setDT_INIPYI(rs.getString("DT_INIPYI"));
        bean.setTX_NOMBREPYI(rs.getString("TX_NOMBREPYI"));
        bean.setTX_DESCPYI(rs.getString("TX_DESCPYI"));
        bean.setTX_ADJUARBP(rs.getString("TX_ADJUARBP"));
        bean.setTX_RUTAARBP(rs.getString("TX_RUTAARBP"));

        bean.setNUM_CODOBJ_PK(rs.getString("NUM_CODOBJ_PK"));
        bean.setNUM_CODOBJESP_PK(rs.getString("NUM_CODOBJESP_PK"));
        bean.setNUM_CODDPTO_PK(rs.getString("NUM_CODDPTO_PK"));
        bean.setNUM_CODPRV_PK(rs.getString("NUM_CODPRV_PK"));
    }

    public String spMan(int tipo, ProyectosInversionVO bean) {
        String msj = "";

        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_ProyectoInversion(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto




            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_PYINV_PK());
            cs.setString(3, bean.getTX_NOMBREPYI());
            cs.setString(4, bean.getTX_DESCPYI());
            cs.setString(5, bean.getDT_INIPYI());
            cs.setString(6, bean.getDT_TERPYI());
            cs.setInt(7, bean.getNUM_SNIPPYI());
            cs.setInt(8, bean.getNUM_CRITERIOPYI());
            cs.setInt(9, bean.getNUM_SANEAPYI());
            cs.setInt(10, bean.getNUM_SANEAPYI2());
            cs.setInt(11, bean.getNUM_SANEAPYI3());
            cs.setInt(12, bean.getNUM_NROREGPYI());
            cs.setInt(13, bean.getNUM_CODACTPER_PK());
            cs.setString(14, bean.getNUM_CODDIST_PK());
            cs.setString(15, bean.getTX_ADJUARBP());
            cs.setString(16, bean.getTX_RUTAARBP());
            cs.setInt(17, bean.getNUM_ANIOPRESUPYI_PK());
            cs.setBigDecimal(18, bean.getNUM_PRESUPYI());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

    public List getListTituProgForTable() throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProyectosInversionVO> lista = new ArrayList<ProyectosInversionVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            //sql = "select * from me_programa";
            //////
            sql = "select ME_PYINV.NUM_PYINV_PK,ME_OBJ.TX_DESCOBJ,ME_OBJESP.TX_DESCOBJESP,ME_ACTPER.NUM_CODACTPER_PK,ME_ACTPER.TX_DESCACTPER,ME_DEPARTAMENTO.TX_NOMDPTO,ME_PROVINCIA.TX_NOMPRV,ME_DISTRITO.TX_NOMDIST,ME_DISTRITO.NUM_CODDIST_PK,ME_PYINV.TX_NOMBREPYI " +
                    //Ini Agregado
                   
                    ", ME_OBJ.num_codobj_pk as codObj, " +
                    " ME_OBJESP.num_codobjesp_pk as codObjEsp, " +
                    " ME_ACTPER.num_codactper_pk as codActPer, " +
                    " ME_DEPARTAMENTO.num_coddpto_pk as codDpto,  " +
                    " ME_PROVINCIA.num_codprv_pk as codProvincia, " +
                    " ME_DISTRITO.num_coddist_pk as codDistrito, " +
                    " ME_PYINV.tx_adjuarbp as rutaAdjunto, " +
                    " ME_PYINV.tx_rutaarbp as ruta, " +

                    " * "
                    //Fin Agregado
                    + " FROM ME_OBJ INNER JOIN ME_OBJESP "
                    + "ON ME_OBJ.NUM_CODOBJ_PK=ME_OBJESP.NUM_CODOBJ_PK "
                    + " INNER JOIN ME_ACTPER "
                    + "ON ME_ACTPER.NUM_CODOBJESP_PK=ME_OBJESP.NUM_CODOBJESP_PK "
                    + "INNER JOIN ME_PYINV "
                    + "ON ME_PYINV.NUM_CODACTPER_PK=ME_ACTPER.NUM_CODACTPER_PK "
                    + "INNER JOIN ME_DISTRITO "
                    + "ON ME_DISTRITO.NUM_CODDIST_PK=ME_PYINV.NUM_CODDIST_PK "
                    + "INNER JOIN ME_PROVINCIA "
                    + "ON ME_PROVINCIA.NUM_CODPRV_PK=ME_DISTRITO.NUM_CODPRV_PK "
                    + "INNER JOIN ME_DEPARTAMENTO "
                    + "ON ME_DEPARTAMENTO.NUM_CODDPTO_PK=ME_PROVINCIA.NUM_CODDPTO_PK";

            ////
            rs = stmt.executeQuery(sql);
            ProyectosInversionVO bean = null;
            while (rs.next()) {
                bean = new ProyectosInversionVO();
                setListTableTituProg(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

//       public List getListTituProgForId(int id) throws SQLException {
//        PreparedStatement pstmt = null;
//        Statement stmt = null;
//        ResultSet rs = null;
//           ArrayList<ProyectosInversionVO> lista = new ArrayList<ProyectosInversionVO>(0);
//        //lista=null;
//        String sql = "";
//        try {
//            conn = DriverManager.getConnection(url, usuario, password);
//
//            sql = "SELECT * FROM ME_PYINV where NUM_PYINV_PK=?";
//            pstmt = conn.prepareStatement(sql);
//            pstmt.setInt(1, id);
//              rs = stmt.executeQuery(sql);
//            ProyectosInversionVO bean = null;
//            while (rs.next()) {
//                bean = new ProyectosInversionVO();
//                setListProgId(bean,rs);
//                lista.add(bean);
//            }
//            rs.close();
//            pstmt.close();
//        } finally {
//            if (rs != null) {
//                rs.close();
//                rs = null;
//            }
//            if (stmt != null) {
//                stmt.close();
//                stmt = null;
//            }
//            if (conn != null) {
//                conn.close();
//                conn = null;
//            }
//
//        }
//        return lista;
//    }
    public ProyectosInversionVO getListTituProgForId(int id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        ProyectosInversionVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select   obj.NUM_CODOBJ_PK, oesp.NUM_CODOBJESP_PK , dep.NUM_CODDPTO_PK, pro.NUM_CODPRV_PK  ,py.NUM_PYINV_PK,py.TX_NOMBREPYI,py.TX_DESCPYI,isnull(convert(varchar,py.DT_INIPYI,103) ,'') as DT_INIPYI , isnull(convert(varchar,py.DT_TERPYI,103) ,'') as DT_TERPYI ,py.NUM_SNIPPYI,py.NUM_CRITERIOPYI ,py.NUM_SANEAPYI,py.NUM_SANEAPYI2,py.NUM_SANEAPYI3,py.NUM_NROREGPYI,py.NUM_CODACTPER_PK,py.NUM_CODDIST_PK,py.TX_DESCPYI  ,py.TX_ADJUARBP,py.TX_RUTAARBP,pr.NUM_ANIOPRESUPYI_PK,pr.NUM_PRESUPYI  from   ME_OBJ obj inner join ME_OBJESP oesp    on   obj.NUM_CODOBJ_PK= oesp.NUM_CODOBJ_PK    inner join ME_ACTPER act on   act.NUM_CODOBJESP_PK=oesp.NUM_CODOBJESP_PK    inner join ME_PYINV py    on py.NUM_CODACTPER_PK=act.NUM_CODACTPER_PK     inner join ME_PRESUPYI pr     on py.NUM_PYINV_PK=pr.NUM_PYINV_PK  inner join ME_DISTRITO dt   on dt.NUM_CODDIST_PK=py.NUM_CODDIST_PK    inner join ME_PROVINCIA pro    on pro.NUM_CODPRV_PK=dt.NUM_CODPRV_PK     inner join ME_DEPARTAMENTO dep     on dep.NUM_CODDPTO_PK=pro.NUM_CODDPTO_PK   where py.NUM_PYINV_PK=?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new ProyectosInversionVO();
            if (rs.next()) {

                setListProgId(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }

        return bean;
    }

//***************************************************************************************
    public List getReportePOI(int id, int anio) throws SQLException {
      //  Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ReportePOIVO> lista = new ArrayList<ReportePOIVO>(0);
        //lista=null;
        PreparedStatement pstmt = null;
        String sql = "";
         Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
          
            //sql = "select * from me_programa";
            //////
            sql = "SELECT        ME_OBJ.NUM_CODOBJ_PK, ME_OBJ.TX_DESCOBJ, ME_OBJESP.NUM_CODOBJESP_PK, ME_OBJESP.TX_DESCOBJESP,  ME_OBJESP.NUM_CODOBJ_PK AS Expr1, ME_ACTPER.TX_DESCACTPER, ME_ACTPER.NUM_CODACTPER_PK, ME_ACTPER.NUM_CODDEPEN_PK,  ME_ACTPER.NUM_CODOBJESP_PK AS Expr2, convert(varchar,ME_ACTPER.DT_INIACTPERC,103) AS DT_INIACTPERC, convert(varchar,ME_ACTPER.DT_INIACTPER,103) AS DT_INIACTPER, convert(varchar,ME_ACTPER.DT_TERACTPERC,103) AS DT_TERACTPERC,  convert(varchar,ME_ACTPER.DT_TERACTPER,103) AS DT_TERACTPER, ME_FISIMETAM.NUM_CODFISIMETAM_PK, ME_FISIMETAM.NUM_MES1FISIMETAM, ME_FISIMETAM.NUM_MES2FISIMETAM,  ME_FISIMETAM.NUM_MES3FISIMETAM, ME_FISIMETAM.NUM_MES4FISIMETAM, ME_FISIMETAM.NUM_MES5FISIMETAM, ME_FISIMETAM.NUM_MES6FISIMETAM,  ME_FISIMETAM.NUM_MES7FISIMETAM, ME_FISIMETAM.NUM_MES8FISIMETAM, ME_FISIMETAM.NUM_MES9FISIMETAM, ME_FISIMETAM.NUM_MES10FISIMETAM, ME_FISIMETAM.NUM_MES11FISIMETAM, ME_FISIMETAM.NUM_MES12FISIMETAM, ME_FISIMETAM.NUM_MES1FISIMETAMC,  ME_FISIMETAM.NUM_MES2FISIMETAMC, ME_FISIMETAM.NUM_MES3FISIMETAMC, ME_FISIMETAM.NUM_MES4FISIMETAMC,  ME_FISIMETAM.NUM_MES5FISIMETAMC, ME_FISIMETAM.NUM_MES6FISIMETAMC, ME_FISIMETAM.NUM_MES7FISIMETAMC,  ME_FISIMETAM.NUM_MES8FISIMETAMC, ME_FISIMETAM.NUM_MES9FISIMETAMC, ME_FISIMETAM.NUM_MES11FISIMETAMC,  ME_FISIMETAM.NUM_MES10FISIMETAMC, ME_FISIMETAM.NUM_MES12FISIMETAMC, ME_FISIMETAM.NUM_CODMETAAPER_PK,  ME_METAAPER.NUM_CODMETAAPER_PK AS Expr3, ME_FINAMETAM.NUM_CODFINAMETAM_PK, ME_FINAMETAM.NUM_MES1FINAMETAM,  ME_FINAMETAM.NUM_MES2FINAMETAM, ME_FINAMETAM.NUM_MES3FINAMETAM, ME_FINAMETAM.NUM_MES4FINAMETAM,  ME_FINAMETAM.NUM_MES5FINAMETAM, ME_FINAMETAM.NUM_MES6FINAMETAM, ME_FINAMETAM.NUM_MES7FINAMETAM,  ME_FINAMETAM.NUM_MES9FINAMETAM, ME_FINAMETAM.NUM_MES10FINAMETAM, ME_FINAMETAM.NUM_MES8FINAMETAM,  ME_FINAMETAM.NUM_MES11FINAMETAM, ME_FINAMETAM.NUM_MES12FINAMETAM, ME_FINAMETAM.NUM_MES1FINAMETAMC,  ME_FINAMETAM.NUM_MES2FINAMETAMC, ME_FINAMETAM.NUM_MES3FINAMETAMC, ME_FINAMETAM.NUM_MES4FINAMETAMC,  ME_FINAMETAM.NUM_MES5FINAMETAMC, ME_FINAMETAM.NUM_MES6FINAMETAMC, ME_FINAMETAM.NUM_MES7FINAMETAMC, ME_FINAMETAM.NUM_MES8FINAMETAMC, ME_FINAMETAM.NUM_MES9FINAMETAMC, ME_FINAMETAM.NUM_MES10FINAMETAMC, ME_FINAMETAM.NUM_MES11FINAMETAMC, ME_FINAMETAM.NUM_MES12FINAMETAMC, ME_FINAMETAM.NUM_CODACTPER_PK AS Expr4,ME_METAAPER.TX_UMACTPER FROM   ME_OBJESP full outer JOIN ME_OBJ ON ME_OBJESP.NUM_CODOBJ_PK = ME_OBJ.NUM_CODOBJ_PK full outer JOIN ME_ACTPER ON ME_OBJESP.NUM_CODOBJESP_PK = ME_ACTPER.NUM_CODOBJESP_PK full outer JOIN ME_METAAPER ON ME_ACTPER.NUM_CODACTPER_PK = ME_METAAPER.NUM_CODACTPER_PK full outer JOIN ME_FISIMETAM ON ME_METAAPER.NUM_CODMETAAPER_PK = ME_FISIMETAM.NUM_CODMETAAPER_PK full outer JOIN  ME_FINAMETAM ON ME_ACTPER.NUM_CODACTPER_PK = ME_FINAMETAM.NUM_CODACTPER_PK WHERE (ME_OBJESP.NUM_CODOBJESP_PK =?) and (YEAR(ME_ACTPER.DT_INIACTPER)=?)";

            ////
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setInt(2, anio);
            rs = pstmt.executeQuery();
          
            ReportePOIVO bean = null;

            while (rs.next()) {
                bean = new ReportePOIVO();
                setReportePOI(bean, rs);
                lista.add(bean);
            }

         
        }catch(SQLException e){
            JOptionPane.showMessageDialog(null, "error nuevo:"+e.toString());
        }
        return lista;
    }
}
